package org.zjh.utils;

import org.zjh.controller.GameController;
import org.zjh.controller.listener.UpdatePlayerOkLis;
import org.zjh.model.Player;
import org.zjh.model.RealPlayer;
import org.zjh.view.ui.GameUi;

import java.sql.*;
import java.util.ArrayList;

/**
 * 数据库工具类
 */
public class MySQLUtil {
    //连接地址
    private static final String URL = DBPropUtil.url;
    //用户名
    private static final String USER = DBPropUtil.user;
    //密码
    private static final String PASSWORD = DBPropUtil.password;


    /**
     * 获取用户信息
     * @param inputAccount 输入的账号
     * @param inputPassword 输入的密码
     * @return true 有对应的用户信息  false 没有对应用户信息
     */
    public static boolean getPlayerInfo(String inputAccount, String inputPassword) {
        //编写SQL语句
        String sql = "SELECT `nickname`,`account`,`score` FROM `user` WHERE `account` = ? AND `password` = ?";
        Connection conn = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            conn = DriverManager.getConnection(URL, USER, PASSWORD);
            statement = conn.prepareStatement(sql);
            statement.setString(1, inputAccount);
            statement.setString(2, inputPassword);
            //得到结果集
            resultSet = statement.executeQuery();
            //判断有没有查询到 有给player赋值 返回true 没有返回false
            if (resultSet.next()) {
                //通过字段名查询对应信息
                String nickname = resultSet.getString("nickname");
                String account = resultSet.getString("account");
                int score = resultSet.getInt("score");
                //获取玩家的昵称
                GameController.getInstance().getPlayer().setNickname(nickname);
                //获取玩家的账号
                GameController.getInstance().getPlayer().setAccount(account);
                //获取玩家的余额
                GameController.getInstance().getPlayer().setScore(score);
                return true;
            } else {
                return false;
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (resultSet != null) resultSet.close();
                if (statement != null) statement.close();
                if (conn != null) conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return false;
    }

    /**
     * 判断数据库中是否存在用户
     * @param inputAccount 输入的账号
     * @param inputNickname 输入的昵称
     * @return true 存在  false 不存在
     */
    public static boolean isExist(String inputAccount, String inputNickname) {
        //编写SQL语句
        String sql = "SELECT `account`,`nickname` FROM `user` WHERE `account` = ? or `nickname` = ?";
        Connection conn = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            conn = DriverManager.getConnection(URL, USER, PASSWORD);
            statement = conn.prepareStatement(sql);
            statement.setString(1, inputAccount);
            statement.setString(2, inputNickname);
            //得到结果集
            resultSet = statement.executeQuery();
            //有值返回 true  没有返回false
            return resultSet.next();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (resultSet != null) resultSet.close();
                if (statement != null) statement.close();
                if (conn != null) conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return false;
    }

    /**
     * 插入数据
     * @param nickname 昵称
     * @param account 账号
     * @param password 密码
     * @param score 积分
     * @param securityIssues1 密保问题一
     * @param answer1 答案一
     * @param securityIssues2 密保问题二
     * @param answer2 答案二
     * @return true：插入成功     false：插入失败
     */
    public static boolean insert(String nickname, String account, String password, int score,
                                 String securityIssues1, String answer1, String securityIssues2, String answer2) {
        //编写SQL语句
        String sql = "INSERT INTO `user`(nickname,account,password,score,security_issues1,answer1,security_issues2,answer2) " +
                "values (?,?,?,?,?,?,?,?)";
        Connection conn = null;
        PreparedStatement statement = null;
        try {
            conn = DriverManager.getConnection(URL, USER, PASSWORD);
            statement = conn.prepareStatement(sql);
            statement.setString(1, nickname);
            statement.setString(2, account);
            statement.setString(3, password);
            statement.setInt(4, score);
            statement.setString(5, securityIssues1);
            statement.setString(6, answer1);
            statement.setString(7, securityIssues2);
            statement.setString(8, answer2);
            //得到结果集
            int i = statement.executeUpdate();
            //判断这个插入条数是否大于0 是返回true 不是false
            return i > 0;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (statement != null) statement.close();
                if (conn != null) conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return false;
    }


    public static void getSecurityIssues(String inputAccount, String inputNickname) {
        //编写SQL语句
        String sql = "SELECT `security_issues1`,`answer1`,`security_issues2`,`answer2` FROM `user` WHERE `account` = ? OR `nickname` = ?";
        Connection conn = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            conn = DriverManager.getConnection(URL, USER, PASSWORD);
            statement = conn.prepareStatement(sql);
            statement.setString(1, inputAccount);
            statement.setString(2, inputNickname);
            //得到结果集
            resultSet = statement.executeQuery();
            //判断有没有查询到 有给player赋值 返回true 没有返回false
            if (resultSet.next()) {
                //通过字段名查询获得对应信息
                String securityIssues1 = resultSet.getString("security_issues1");
                String securityIssues2 = resultSet.getString("security_issues2");
                String answer1 = resultSet.getString("answer1");
                String answer2 = resultSet.getString("answer2");
                //给 找回密码面板中 密保问题标签 设置内容
                GameUi.updatePlayerInfoFrame.getUpdatePlayerInfoPanel().getSecurityIssuesLabel1().setText("密保问题一：" + securityIssues1);
                GameUi.updatePlayerInfoFrame.getUpdatePlayerInfoPanel().getSecurityIssuesLabel2().setText("密保问题二：" + securityIssues2);
                UpdatePlayerOkLis.getInstance().setCorrectAnswer1(answer1);
                UpdatePlayerOkLis.getInstance().setCorrectAnswer2(answer2);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (resultSet != null) resultSet.close();
                if (statement != null) statement.close();
                if (conn != null) conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 修改用户信息
     * @param inputNickname 昵称
     * @param newNickname 新昵称
     * @param inputAccount 输入的账号
     * @param newPassword  新密码
     */
    public static boolean updatePlayerInfo(String inputNickname, String newNickname, String inputAccount, String newPassword) {
        //SQL语句
        String sql;
        //连接对象
        Connection conn = null;
        ///执行者对象
        PreparedStatement statement = null;
        try {
            conn = DriverManager.getConnection(URL, USER, PASSWORD);
            // 判断用户是找回密码还是在游戏中进行账号设置修改
            if (inputAccount == null || inputNickname == null) {
                inputAccount = GameController.getInstance().getPlayer().getAccount();
                inputNickname = GameController.getInstance().getPlayer().getNickname();
            }

            //判断是修改用户名还是密码还是全部修改
            if (newNickname == null || newNickname.isEmpty()) {
                //只修改密码 可能是忘记密码修改 或者 登入后修改 忘记密码修改需要有两种方式验证
                sql = "UPDATE `user` SET `password` = ? WHERE `account` = ? OR `nickname` = ?";
                statement = conn.prepareStatement(sql);
                statement.setString(1, newPassword);
                statement.setString(2, inputAccount);
                statement.setString(3, inputNickname);
            } else if (newPassword.isEmpty()) {
                //只修改名称 修改名称需要登入后的才能修改不需要两个验证
                sql = "UPDATE `user` SET `nickname` = ? WHERE `account` = ? OR `nickname` = ?";
                statement = conn.prepareStatement(sql);
                statement.setString(1, newNickname);
                statement.setString(2, inputAccount);
                statement.setString(3, newNickname);
            } else {
                //都修改 是登入后的操作只需要校验账号
                sql = "UPDATE `user` SET `nickname` = ?,`password` = ? WHERE `account` = ?";
                statement = conn.prepareStatement(sql);
                statement.setString(1, newNickname);
                statement.setString(2, newPassword);
                statement.setString(3, inputAccount);
            }
            //执行修改语句并且返回结果 大于0 修改成功 不然修改失败
            return statement.executeUpdate() > 0;

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (statement != null) statement.close();
                if (conn != null) conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return false;
    }


    /**
     * 获取积分前十名玩家信息
     * @return 玩家集合
     */
    public static ArrayList<Player> getTop10PlayersInfo(){
        ArrayList<Player> top10Players = new ArrayList<>();
        //编写SQL语句
        String sql = "SELECT `nickname`, `score` FROM `user` ORDER BY score DESC LIMIT 10";
        Connection conn = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            conn = DriverManager.getConnection(URL, USER, PASSWORD);
            statement = conn.prepareStatement(sql);
            //得到结果集
            resultSet = statement.executeQuery();
            //判断有没有查询到
            while (resultSet.next()) {
                //通过字段名查询对应信息
                String nickname = resultSet.getString("nickname");
                int score = resultSet.getInt("score");
                // 将玩家信息添加到 集合
                top10Players.add(new RealPlayer(nickname,null,null,score));
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (resultSet != null) resultSet.close();
                if (statement != null) statement.close();
                if (conn != null) conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return top10Players;
    }


    /**
     * 更新玩家积分
     * @param realPlayer 实际玩家
     * @param
     */
    public static void updatePlayerScore(RealPlayer realPlayer){
        //SQL语句
        String sql;
        //连接对象
        Connection conn = null;
        ///执行者对象
        PreparedStatement statement = null;
        try {
            conn = DriverManager.getConnection(URL, USER, PASSWORD);

            sql = "UPDATE `user` SET `score` = ? WHERE `account` = ?";
            statement = conn.prepareStatement(sql);
            statement.setInt(1, realPlayer.getScore());
            statement.setString(2, realPlayer.getAccount());
            statement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (statement != null) statement.close();
                if (conn != null) conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}
